<?php
if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class M_kepesertaan_peserta extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	function get() {
		$sql = 'SELECT * FROM peserta
		INNER JOIN penduduk ON peserta.nik = penduduk.nik
		INNER JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		INNER JOIN master_kelurahan ON keluarga.kd_kelurahan = master_kelurahan.kd_kelurahan
		ORDER BY kd_peserta DESC';
		return $sql;
	}

	function cek_peserta($nik) {
		$sql = 'SELECT * FROM peserta WHERE nik=?';
		$query = $this->db->query($sql, $nik);
        return $query;
	}

	function get_cari($keyword='') {
		$sql = 'SELECT * FROM peserta
		INNER JOIN penduduk ON peserta.nik = penduduk.nik
		INNER JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		INNER JOIN master_kelurahan ON keluarga.kd_kelurahan = master_kelurahan.kd_kelurahan
		WHERE penduduk.nik like "%'.$keyword.'%" OR kd_peserta like "%'.$keyword.'%" OR nama like "%'.$keyword.'%"
		ORDER BY kd_peserta DESC';
		return $sql;
	}

	function get_export() {
		$sql = 'SELECT * FROM peserta
		INNER JOIN penduduk ON peserta.nik = penduduk.nik
		INNER JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		INNER JOIN master_kelurahan ON keluarga.kd_kelurahan = master_kelurahan.kd_kelurahan
		ORDER BY kd_peserta DESC';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}


	function get_by($params) {
		$sql = 'SELECT * FROM peserta
		INNER JOIN penduduk ON peserta.nik = penduduk.nik
		INNER JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk 
		WHERE kd_peserta = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function delete($kd_peserta) {
		$this->db->where('kd_peserta', $kd_peserta);
		$delete = $this->db->delete('peserta');
		return $delete;
	}

	function get_penduduk($nik) {
		$sql = 'SELECT * FROM penduduk 
		INNER JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		WHERE nik = "'.$nik.'" LIMIT 1';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_keluarga($kd_kk) {
		$sql = 'SELECT * FROM keluarga WHERE kd_kk = "'.$kd_kk.'" LIMIT 1';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_agama() {
		$sql = 'SELECT * FROM master_agama';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_pendidikan() {
		$sql = 'SELECT * FROM master_pendidikan';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_pekerjaan() {
		$sql = 'SELECT * FROM master_pekerjaan';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_status_nikah() {
		$sql = 'SELECT * FROM master_status_nikah';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_shdk() {
		$sql = 'SELECT * FROM master_shdk';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_jenis_peserta() {
		$sql = 'SELECT * FROM master_jenis_peserta';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_kelurahan() {
		$sql = 'SELECT * FROM master_kelurahan';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function cek_keluarga($kd_kk) {
		$sql 	= 'SELECT * FROM keluarga WHERE kd_kk = ?';
		$query 	= $this->db->query($sql, $kd_kk);
        return $query;
	}

	function save_keluarga($dataInsert) {
		$query = $this->db->insert('keluarga', $dataInsert);
		return $query;
	}

	function update_keluarga($kd_kk, $dataUpdate) {
		$this->db->where('kd_kk', $kd_kk);
		$update = $this->db->update('keluarga', $dataUpdate);
		return $update;
	}

	function cek_penduduk($nik) {
		$sql 	= 'SELECT * FROM penduduk WHERE nik = ?';
		$query 	= $this->db->query($sql, $nik);
        return $query;
	}

	function save_penduduk($dataInsert) {
		$query = $this->db->insert('penduduk', $dataInsert);
		return $query;
	}

	function update_penduduk($nik, $dataUpdate) {
		$this->db->where('nik', $nik);
		$update = $this->db->update('penduduk', $dataUpdate);
		return $update;
	}

	function get_kode_wilayah($kd_kelurahan) {
		$sql = 'SELECT * FROM master_kelurahan WHERE kd_kelurahan = ?';
		$query = $this->db->query($sql, $kd_kelurahan);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_kode_max($kode) {
		$sql = "SELECT max(kd_peserta) AS kd_max FROM peserta WHERE kd_peserta LIKE '".$kode."%'";
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['kd_max'];
	}

	function save_peserta($dataInsert) {
		$query = $this->db->insert('peserta', $dataInsert);
		return $query;
	}
}
